package com.example.db;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.esotericsoftware.kryo.Kryo;
import com.esotericsoftware.kryo.io.Output;
import com.example.db.bean.User;
import com.example.db.db.DBHelper;
import com.example.db.utils.TimeUtils;

import junit.framework.TestCase;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * Created by kkmike999 on 16/2/13.
 */
public class SqliteTest extends TestCase {

	public static final int COUNT = 10000;

	SQLiteDatabase database;

	User[]        users   = UserHelper.createUserList(COUNT);
	List<Integer> uidList = UserHelper.getUidList(users);

	@Before
	public void setUp() throws Exception {
		database = new DBHelper(DbApp.getContext(), "kvdb_" + (int) (Math.random() * 100)).getWritableDatabase();

		try {
			database.execSQL("create table kv (_id int PRIMARY KEY, key varchar, value BLOB)");
			database.execSQL("create table user (_id int PRIMARY KEY, uid int, gender int, name varchar, faceurl varchar)");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@After
	public void tearDown() throws Exception {
		database.execSQL("DROP TABLE kv");
		database.close();
	}

	@Test
	public void insertBytes() {
		String insertSQL = "INSERT INTO @tableName (key,value) VALUES (?,?)".replace("@tableName", "kv");

		String value = "abc";
		database.execSQL(insertSQL, new Object[]{"key0", value.getBytes()});
	}

	@Test
	public void insertUserByStatement() throws IOException {
		TimeUtils.begin("put bytes");

		database.beginTransaction();

		String insertSQL = "INSERT INTO @tableName (key,value) VALUES (?,?)".replace("@tableName", "kv");

		SQLiteStatement statement = database.compileStatement(insertSQL);

		Kryo kryo = new Kryo();
		kryo.register(User.class);

		ByteArrayOutputStream bout   = new ByteArrayOutputStream();
		Output                output = new Output(bout);

		for (User user : users) {
			kryo.writeObject(output, user);

			byte[] bytes = bout.toByteArray();

			output.clear();
			bout.reset();

			statement.bindString(1, user.name);
			statement.bindBlob(2, bytes);

			statement.executeInsert();
//            database.execSQL(insertSQL, new Object[]{user.name, KryoUtil.toBytes(user)});
		}

		statement.close();
		output.close();
		bout.close();

		database.setTransactionSuccessful();
		database.endTransaction();

		System.out.println("SqliteTest(" + users.length + ") : put bytes time = " + TimeUtils.time("put bytes"));
	}

	@Test
	public void sqlInsertUser() throws IOException {
		TimeUtils.begin("put User");

		database.beginTransaction();

		String insertSQL = "INSERT INTO @tableName (uid,gender,name,faceurl) VALUES (?,?,?,?)".replace("@tableName", "user");

		SQLiteStatement statement = database.compileStatement(insertSQL);

		for (User user : users) {

			statement.bindLong(1, user.uid);
			statement.bindLong(2, user.gender);
			statement.bindString(3, user.name);
			statement.bindString(4, user.faceurl);

			statement.executeInsert();
//            database.execSQL(insertSQL, new Object[]{user.uid, user.gender, user.name, user.faceurl});
		}

		statement.close();

		database.setTransactionSuccessful();
		database.endTransaction();

		System.out.println("SqliteTest(" + users.length + ") : put user (statement)time = " + TimeUtils.time("put User"));
	}

	@Test
	public void sqlDelObj() throws IOException {
		sqlInsertUser();

		String uidIn = " uid in" + uidList.toString().replace("[", "(").replace("]", ")");

		TimeUtils.begin();

		database.beginTransaction();

		String sql = "DELETE FROM user WHERE " + uidIn;

//		System.out.println(sql);
		database.execSQL(sql);

//        for (User user : users) {
//            database.execSQL("DELETE FROM " + table + " WHERE uid=" + user.uid);
//        }

		database.setTransactionSuccessful();
		database.endTransaction();

		System.out.println("SqliteTest(" + users.length + "): del Obj time = " + TimeUtils.time());
	}

	@Test
	public void sqlGetObj() throws Exception {
		sqlInsertUser();

		String uidIn = " uid in" + uidList.toString().replace("[", "(").replace("]", ")");

		TimeUtils.begin();

		database.beginTransaction();

		String sql = "SELECT * FROM user WHERE " + uidIn;

		Cursor cursor = database.rawQuery(sql, new String[0]);

		int uidIndex     = cursor.getColumnIndex("uid");
		int nameIndex    = cursor.getColumnIndex("name");
		int faceurlIndex = cursor.getColumnIndex("faceurl");
		int genderIndex  = cursor.getColumnIndex("gender");

		while (cursor.moveToNext()) {
			User user = new User();

			user.uid = cursor.getInt(uidIndex);
			user.gender = cursor.getInt(genderIndex);
			user.name = cursor.getString(nameIndex);
			user.faceurl = cursor.getString(faceurlIndex);
		}

		database.setTransactionSuccessful();
		database.endTransaction();

		System.out.println("SqliteTest(" + users.length + "): get Obj time = " + TimeUtils.time());
	}
}
